package com.neusoft.elm.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.neusoft.elm.dao.BusinessDao;
import com.neusoft.elm.po.Admin;
import com.neusoft.elm.po.Business;
import com.neusoft.elm.util.DBUtil;

public class BusinessDaoImpl implements BusinessDao {
	Connection con = null;
	PreparedStatement pst = null;
	ResultSet rs = null; 
	
	@Override
	public List<Business> listBusiness(String businessName, String businessAddress){
//		String sql = "SELECT * FROM  business WHERE 1=1 ";
		
		StringBuffer sql = new StringBuffer("SELECT * FROM  business WHERE 1=1 ");
//		商家名称
		if(businessName != null && !businessName.equals("")){
			sql.append("AND businessName LIKE '%"
					+ businessName
					+ "%' ");
		}
		
//		商家地址
		if(businessAddress != null && !businessAddress.equals("")){
			sql.append("AND businessAddress LIKE '%"
					+ businessAddress
					+ "%' ");
		}
		
		List<Business> list = new ArrayList<Business>();
		
		try {
			con = DBUtil.getConnection();
			pst = con.prepareStatement(sql.toString());
			rs = pst.executeQuery();
			
			while(rs.next()){
				
				Business business = new Business();
				business.setBusinessAddress(rs.getString("businessAddress"));
				business.setBusinessExplain(rs.getString("businessExplain"));
				business.setBusinessId(rs.getInt("businessId"));
				business.setBusinessName(rs.getString("businessName"));
				business.setDeliveryPrice(rs.getDouble("deliveryPrice"));
				business.setPassword(rs.getString("password"));
				business.setStarPrice(rs.getDouble("starPrice"));
				
				list.add(business);	
				
			}
		} catch (Exception e) {
			// 
			e.printStackTrace();
		} finally{
			DBUtil.closeAll(rs, pst, con);
		}
		
		return list;
	}

	@Override
	public int saveBusiness(String businessName) {
		int businessId = -1;
		String sql = "INSERT INTO business (businessName,`password`) VALUES (?,123) ";
		
		try {
			con = DBUtil.getConnection();
//			TODO 理解：PreparedStatement.RETURN_GENERATED_KEYS
			pst = con.prepareStatement(sql,PreparedStatement.RETURN_GENERATED_KEYS);
			pst.setString(1, businessName);
			pst.executeUpdate();
//			TODO 理解：获取自动增长列(一行一列）
			rs = pst.getGeneratedKeys();
			if(rs.next()){
				businessId = rs.getInt(1);//getInt：获取 结果集第一列的值
			}
			
			
			
		} catch (SQLException e) {
			// 
			e.printStackTrace();
		} finally{
			DBUtil.closeAll(rs, pst, con);
		}
		
		return businessId;
	}

	@Override
	public int removeBusiness(int businessId) {
		String delFoodSql = "DELETE FROM food WHERE businessId = ?";
		String delBusinessSql = "DELETE FROM business WHERE businessId = ?";
		int result = -1;
		
		try {
//			1.连接数据库
			con = DBUtil.getConnection();

//			2.开启一个事务
//			TODO 理解：setAutoCommit(false)
			con.setAutoCommit(false);
			
//			删除食品信息
			pst = con.prepareStatement(delFoodSql);
			pst.setInt(1, businessId);
			pst.executeUpdate();
//			删除商家信息
			pst = con.prepareStatement(delBusinessSql);
			pst.setInt(1, businessId);
			result = pst.executeUpdate();
			
//			事务提交
			con.commit();
			
			
			
		} catch (SQLException e) {
			result = 0;
//			事务回滚
//			TODO 为什么在try块中开启的事务可以在catch中回滚？
			try {
				con.rollback();
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		} finally{
			DBUtil.closeAll(rs, pst, con);
		}
		
		return result;
	}

	@Override
	public Business getBusinessByIdByPass(Integer businessId, String password) {
		
		String sql = "SELECT * FROM  business WHERE businessId = ? AND password = ? ";
		Business business = null;
		
		try {
//			
			con = DBUtil.getConnection();

			pst = con.prepareStatement(sql);
			pst.setInt(1, businessId);
			pst.setString(2, password);

			rs = pst.executeQuery();
			
			
			while (rs.next()){
				business = new Business();
				business.setBusinessAddress(rs.getString("businessAddress"));
				business.setBusinessExplain(rs.getString("businessExplain"));
				business.setBusinessId(rs.getInt("businessId"));
				business.setBusinessName(rs.getString("businessName"));
				business.setDeliveryPrice(rs.getDouble("deliveryPrice"));
				business.setPassword(rs.getString("password"));
				business.setStarPrice(rs.getDouble("starPrice"));
				
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			DBUtil.closeAll(rs, pst, con);
		}
		
		return business;
	}

	@Override
	public Business showBusiness(Integer businessId) {
		String sql = "SELECT * FROM  business WHERE businessId = ? ";
		
		Business business = null;
		
		try {
//			
			con = DBUtil.getConnection();

			pst = con.prepareStatement(sql);
			pst.setInt(1, businessId);

			rs = pst.executeQuery();
			
			
			while (rs.next()){
				business = new Business();
				business.setBusinessAddress(rs.getString("businessAddress"));
				business.setBusinessExplain(rs.getString("businessExplain"));
				business.setBusinessId(rs.getInt("businessId"));
				business.setBusinessName(rs.getString("businessName"));
				business.setDeliveryPrice(rs.getDouble("deliveryPrice"));
				business.setPassword(rs.getString("password"));
				business.setStarPrice(rs.getDouble("starPrice"));
				
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			DBUtil.closeAll(rs, pst, con);
		}
		
		return business;
	}

	@Override
	public int updateBusiness(Business business) {
		int result = -1;
		String sql = "UPDATE business SET businessName = ? , businessAddress = ?, "
				+ "businessExplain = ?, starPrice = ? , deliveryPrice = ? "
				+ "WHERE businessId = ? ";
		
		try {
			con = DBUtil.getConnection();

			pst = con.prepareStatement(sql);
			pst.setString(1, business.getBusinessName());
			pst.setString(2, business.getBusinessAddress());
			pst.setString(3, business.getBusinessExplain());
			pst.setDouble(4, business.getStarPrice());
			pst.setDouble(5, business.getDeliveryPrice());
			pst.setInt(6, business.getBusinessId());
//			TODO 总结各种execute方法的使用场景
			result = pst.executeUpdate();
			
		} catch (SQLException e) {
			// 
			e.printStackTrace();
		} finally{
			DBUtil.closeAll(rs, pst, con);
		}
		
		return result;
	}

	@Override
	public int updateBusinessByPassword(Integer businessId,String password) {
		int result = -1;
		String sql = "UPDATE business SET password = ? WHERE businessId = ? ";
		
		try {
			con = DBUtil.getConnection();

			pst = con.prepareStatement(sql);
			pst.setString(1, password);
			pst.setInt(2,businessId);
			
			result = pst.executeUpdate();
			
		} catch (SQLException e) {
			// 
			e.printStackTrace();
		} finally{
			DBUtil.closeAll(rs, pst, con);
		}
		
		return result;
	}

	
}
